package com.scalar.chiptrack.adaptor.ikanos.forecast;

import com.scalar.chiptrack.jobs.JobHandler;
import com.scalar.chiptrack.utils.Logger;
import com.scalar.chiptrack.utils.ConfigManager;
import com.scalar.chiptrack.utils.StringUtils;
import com.scalar.chiptrack.utils.Mailer;
import com.scalar.chiptrack.adaptor.AdaptorUtils;
import com.scalar.chiptrack.adaptor.utils.ParserResult;
import com.scalar.chiptrack.adaptor.utils.Parser;
import com.scalar.chiptrack.adaptor.utils.ParserFactory;
import com.scalar.chiptrack.adaptor.utils.XLSParserImpl;
import com.scalar.chiptrack.adaptor.dao.AdaptorRunInfo;
import com.scalar.chiptrack.db.DataSource;
import com.scalar.chiptrack.sales.LineItem;
import com.scalar.chiptrack.users.notification.dao.NotificationDAO;
import com.scalar.chiptrack.commons.Constants;
import com.scalar.ScalarException;

import java.util.ArrayList;
import java.io.File;

import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.SQLException;
import java.sql.CallableStatement;

/**
 * Created by IntelliJ IDEA.
 * User: munugoti
 * Date: 1 Apr, 2009
 * Time: 3:57:50 PM
 * To change this template use File | Settings | File Templates.
 */

public class ForecastParser extends JobHandler
{
    private Logger m_parserLogger   = null;
    private String m_dataSourceDir  = null;

    public ForecastParser() throws Exception
    {
        try
        {
            m_dataSourceDir = ConfigManager.getForcastPortingFileDir() ;

            if ( m_dataSourceDir != null )
            {
                AdaptorUtils.makeAllAdaptorDirs( m_dataSourceDir );

                m_parserLogger = AdaptorUtils.getDataParserLogger( m_dataSourceDir, "SalesForeCast" );
                AdaptorUtils.setLogger( m_parserLogger );
            }
        }
        catch ( Exception e )
        {
            e.printStackTrace();
        }
    }

    public static void main ( String[] agrs ) throws Exception
    {
        ForecastParser parser = new ForecastParser();
        parser.execute();
    }
    // To be implemented by sub classes
    public void execute() throws Exception
    {
        boolean success = false;
        AdaptorUtils.printHeader("SALES FORECAST PORTING", CONSTANT_PARSER );

        ArrayList successFileList      = new ArrayList();
        File doneDestDir = new File( m_dataSourceDir + "/done" );
        File dataoutDir  = new File( m_dataSourceDir );

        File[] fileList = dataoutDir.listFiles();

        if ( ( fileList == null ) || ( fileList.length <= 3 ) )
        {
            log( "No file found in directory: " + dataoutDir );
            throw new ScalarException( "\n+-----------No file found in directory " + dataoutDir + "-----\n\n" );
        }

        for ( int p = 0; p < fileList.length; p++ )
        {               
            if ( fileList[p].isDirectory() || !fileList[p].getName().startsWith( "SALES FORECAST" ) )
            {
                continue;
            }
            log ( "Parsing Sale Forecast File:"  + fileList[p].getName() );
            System.out.println ( "Parsing Sales Forecast File:"  + fileList[p].getName());
            ArrayList<LineItem> salesForecastList = new ArrayList<LineItem>();
            try
            {
                //TO PARSE THE SALES FORECAST SHEET
                salesForecastList = parseShippingFile ( fileList[p] , "Sales Forecast" );
                success = true;
            }
            catch (Exception e)
            {
                success = false;
                e.printStackTrace();
            }

            String portingStatusMsg = null;

            if ( success ) //SEND PORTING STATUS MAIL
            {
                log ( "Porting Sales Forecast Details completed successfully...." );
                System.out.println ( "Porting Sales Forecast Details completed successfully...." );
                log ( "Sending porting status mail..." );
                portingStatusMsg = preparePortingStatusMsg(salesForecastList);
                if ( portingStatusMsg == null || portingStatusMsg.equals( "" ) )
                {
                    log ( "WARNING!! WARNING!! Porting Status Message is Empty, not sending mail" );
                    System.out.println ( "WARNING!! WARNING!! Porting Status Message is Empty, not sending mail" );
                }
                else
                {
                    try
                    {
                        StringBuffer msg = new StringBuffer ( );
                        msg.append ( "<HTML><HEAD><TITLE>Sales Forecast Porting Status</TITLE></HEAD>" );
                        msg.append ( "<BODY ALIGN='CENTER'><TABLE STYLE='FONT-SIZE: 8PT; COLOR: BLACK; FONT-FAMILY: VERDANA' BORDERCOLOR='#FFFFFF' CELLSPACING='1' CELLPADDING='3' BORDER=0 ALIGN='CENTER'>" );
                        msg.append ( "<TR BGCOLOR='#CCCCFF'><TD COLSPAN='4' ALIGN='CENTER'><B>SALES FORECAST PORTING STATUS REPORT</B></TD></TR>" );
                        msg.append ( "<TR BGCOLOR='#D3D3D6' ALIGN='CENTER'>" );
                        msg.append ( "<TD><B>S.No</B></TD><TD><B>CUSTOMER NAME</B></TD><TD><B>SALES PART NUMBER</B></TD><TD><B>REMARKS</B></TD>" );
                        msg.append ( "</TR>" );
                        msg.append ( portingStatusMsg );
                        msg.append ( "</TABLE></BODY></HTML>" );
                        String fromEmail = ConfigManager.getScalarAdminEmail();
                        sendSalesForecastPortingAlert( "Sales Forecast porting status alert", msg.toString(), fromEmail, Constants.SALES_ORDER_PORTING_NOTIFICATION);
                        log ( "Porting status mail sent successfully" );
                        System.out.println ( "Porting status mail sent successfully" );
                    }
                    catch ( ScalarException e )
                    {
                        log ( "**WARNING** Could not send Porting Status alert mail:" + e.getRootCause() );
                        System.out.println ( "***WARNING*** Could not send Porting Status alert mail" );
                        e.printStackTrace();
                    }
                }
            }

            successFileList.add( fileList[p] );

            int adaptor_id = AdaptorRunInfo.getLastRunAdaptorId(ForecastAdaptor.ADAPTOR_NAME, CONSTANT_ADAPTOR);

            if ( success )
            {
                AdaptorRunInfo.saveAdaptorLastUpdatedStatus(CONSTANT_PARSER, ForecastAdaptor.ADAPTOR_NAME, CONSTANT_SUCCESS, adaptor_id);
                System.out.println( "SUCCESSFULLY UPDATED IN THE DATABASE" );

                //Move the files to Done directory
                AdaptorUtils.moveFilesToDone( successFileList , doneDestDir );
            }
        }
    }

    private ArrayList<LineItem> parseShippingFile( File shipFile , String sheetName) throws Exception
    {
        if ( shipFile == null ) throw new ScalarException( "Filename argument is null" );

        ParserResult result     = null;
        Connection conn         = null;
		PreparedStatement pstmt = null;
        CallableStatement cstmt = null;
        boolean success = false;
        LineItem lineItem       = null;
        ArrayList forecastList  = new ArrayList();

        conn = DataSource.getConnection();

        try
        {
            log( "\nFound " + shipFile.getName() );
            log( "\nParsing the file for Sales Forecast Report ..." );
            System.out.println( "\nFound " + shipFile.getName() );
            System.out.println( "\nParsing the file for Sales Forecast Report..." );

            Parser parser = ParserFactory.getXLSParser( shipFile , sheetName );

            ((XLSParserImpl)parser).setHeaderRowIdx(9);
            ((XLSParserImpl)parser).setDataStartRowIdx(10);

            result = parser.parse();
            //System.out.println("result.getHeadings() = " + result.getHeadings());
            pstmt = conn.prepareStatement( "INSERT INTO FCENTRYHISTORY SELECT * FROM FCENTRYMASTER" );
            pstmt.executeUpdate();
            if ( pstmt != null )
            {
                pstmt.close();
                pstmt = null;
            }

            pstmt = conn.prepareStatement( "DELETE FROM FCENTRYMASTER" );
            pstmt.executeUpdate();
            if ( pstmt != null )
            {
                pstmt.close();
                pstmt = null;
            }
            cstmt = conn.prepareCall( " { CALL SP_UPDATE_FORECAST_DETAILS (?, ?, ?, ?, ?, ? ) }" );
            result = parser.parse();
            while ( result.next() )
            {

                if ( result.getString("Oracle ordering Customer") != null && result.getString("Oracle ordering Customer").trim().length() >0 )
                {
                    lineItem = new LineItem();
                    lineItem .setMarketingPartNo( result.getString("Marketing Part Number") );
                    lineItem.setCustomerName( result.getString("Oracle ordering Customer") );
                    lineItem.setManufacturingPartNo( result.getString("Base Mfg PN") );

                    cstmt.setString(1, result.getString("Marketing Part Number") );
                    cstmt.setString(2, result.getString("Oracle ordering Customer"));
                    cstmt.setString(3, lineItem.getManufacturingPartNo() );

                    for ( int i = 0; i < 4; i++ )
                    {                           
                        cstmt.setInt(4 , i);
                        cstmt.setInt(5, result.getInt( 5 + i ) );
                        cstmt.registerOutParameter( 6, java.sql.Types.INTEGER );

                        cstmt.execute();

                        int portingStatusCode = cstmt.getInt( 6 );

                        if ( i == 0 )
                        {
                            lineItem.setPortingStatusCode( portingStatusCode );
                        }
                        if ( (portingStatusCode&1) == 1 )
                        {
                            break;
                        }
                    }

                   forecastList.add( lineItem );
                }
            }
            if ( conn != null )
            {
                conn.commit();
                success = true;
            }

        }
        catch ( Exception ex )
        {
            success = false;
            System.out.println("ERROR!!!ERROR!!! While Parsing the Sales Forecast Report");
            ex.printStackTrace();
            throw ex;
        }
        finally
        {
            if ( !success && conn != null )
            {
                try
                {
                    conn.rollback();
                } catch ( SQLException ignore ) {}
            }
            if ( cstmt != null )
            {
                try { cstmt.close(); } catch ( SQLException ignore ) {}
            }
            if ( conn != null )
            {
                try { conn.close(); } catch ( SQLException ignore ) {}
            }
        }

        return forecastList;

    }// end of prase Forecast Report method



    private String preparePortingStatusMsg ( ArrayList lineItemList )
    {
        final int SUCCESS = 0;
        final int SALES_PARTNUMBER_NOT_FOUND = 1;
        final int CUSTOMER_CREATED = 2;

        LineItem lineItem   = null;
        StringBuffer statusMsg  = new StringBuffer();
        int lineItemListSize = ( lineItemList == null ) ? 0 : lineItemList.size();
        int count               = 0;
        for ( int i = 0; i < lineItemListSize; i++ )
        {
            lineItem = (LineItem)lineItemList.get( i );

            if ( (lineItem.getPortingStatusCode()&SALES_PARTNUMBER_NOT_FOUND) == SALES_PARTNUMBER_NOT_FOUND ||
                    (lineItem.getPortingStatusCode()&CUSTOMER_CREATED) == CUSTOMER_CREATED   )
            {
                statusMsg.append ( "<TR BGCOLOR='#E6E6E6' ALIGN='CENTER'>" );
                statusMsg.append ( "<TD ALIGN='LEFT'>" ).append ( ++count ).append ( "</TD>" );
                statusMsg.append ( "<TD ALIGN='LEFT'>" ).append ( (lineItem.getCustomerName()==null) ? "" : lineItem.getCustomerName() ).append ( "</TD>" );
                statusMsg.append ( "<TD ALIGN='CENTER'>" ).append ( (lineItem.getMarketingPartNo()==null) ? "" : lineItem.getMarketingPartNo() ).append ( "</TD>" );
                statusMsg.append ( "<TD ALIGN='LEFT'>" );
                if ( (lineItem.getPortingStatusCode()&SALES_PARTNUMBER_NOT_FOUND) == SALES_PARTNUMBER_NOT_FOUND )
                {
                    statusMsg.append ( "<B>Marketing Part Number</B> not found for product<BR> <B>" + lineItem.getManufacturingPartNo()  + "</B>");
                }

                if ( (lineItem.getPortingStatusCode()&CUSTOMER_CREATED) == CUSTOMER_CREATED )
                {
                    statusMsg.append ( "<B>New Customer</B> created<BR>" );
                }

                statusMsg.append ( "</TD>" );
                statusMsg.append ( "</TR>");

            }
        }
        return statusMsg.toString();
    }

    private void sendSalesForecastPortingAlert( String subject, String message, String fromEmail, String notificationType ) throws Exception
    {
        ArrayList emailList = NotificationDAO.loadEmailsByNotificationType(null, notificationType );
        String[] emails = new String[emailList.size()];
        emailList.toArray(emails);
        String toEmails = StringUtils.concatStrings( emails, Mailer.EMAIL_SEPARATOR );
        log ( "Sending sales forecast porting alerts to: " + toEmails );
        System.out.println ( "Sending sales forecast porting alerts to: " + toEmails );
        try
        {
            Mailer.send ( fromEmail, toEmails, null, null, subject, message, null, Mailer.CONTENT_TYPE_HTML, false );
        }
        catch( Exception e )
        {
            throw new ScalarException ( "Could not send email notifications", e );
        }
    }

    private void log( String message )
    {
        if ( message == null ) return;

        if ( m_parserLogger != null )
        {
            m_parserLogger.logDebug( message + LINE_SEPARATOR );
        }
        else
        {
            System.out.println( message );
        }
    }

}

